const connection = require('../app/database');

class ArticleService {
  // 创建一篇文章
  async create(userId, title, classifyId, content, count) {
    try {
      const statement = `INSERT INTO article (user_id, title, classify_id, content, count) VALUES (?, ?, ?, ?, ?)`;
      const result = await connection.execute(statement, [userId, title, classifyId, content, count]);
      return result[0];
    } catch (error) {
      console.log(error);
    }
  }

  // 删除一篇文章
  async remove(userId, articleId) {
    try {
      const statement = `DELETE FROM article WHERE user_id = ? AND id = ?`;
      const result = await connection.execute(statement, [userId, articleId]);
      return result[0];
    } catch (error) {
      console.log(error);
    }
  }

  // 修改一篇文章
  async update(userId, title, classifyId, content, count, articleId) {
    try {
      const statement = `UPDATE article SET title = ?, classify_id = ?, content = ?, count = ? WHERE user_id = ? AND id = ?`;
      const result = await connection.execute(statement, [title, classifyId, content, count, userId, articleId]);
      return result[0];
    } catch (error) {
      console.log(error);
    }
  }

  // 获取一篇文章
  async detail(articleId) {
    try {
      const statement = `
                        SELECT 
                          a.id id,
                          a.title title,
                          a.content content,
                          JSON_OBJECT('id', u.id, 'nickname', u.nickname, 'avatarUrl', u.avatar_url, 'coverUrl', u.cover_url, 'isPrivate', u.private, 'createTime', u.createAt) author,
                          a.classify_id classifyId,
                          a.count count,
                          a.likes likes,
                          a.view_count viewCount,
                          ac.classify classifyName,
                          a.createAt createTime,
                          a.updateAt updateTime
                        FROM article a
                        LEFT JOIN user u ON a.user_id = u.id
                        LEFT JOIN article_classify ac ON a.classify_id = ac.id
                        WHERE a.id = ?`;
      const [result] = await connection.execute(statement, [articleId]);
      return result[0];
    } catch (error) {
      console.log(error);
    }
  }

  // 获取文章列表
  async list(userId, offset, limit) {
    try {
      const statement = `
                        SELECT 
                          a.id 'key',
                          a.title title,
                          a.content content,
                          JSON_OBJECT('id', u.id, 'nickname', u.nickname, 'avatarUrl', u.avatar_url, 'coverUrl', u.cover_url, 'isPrivate', u.private, 'createTime', u.createAt) author,
                          a.classify_id classifyId,
                          a.count count,
                          ac.classify classifyName,
                          a.likes likes,
                          a.view_count viewCount,
                          (SELECT count(*) FROM article) total,
                          a.createAt createTime,
                          a.updateAt updateTime
                        FROM article a
                        LEFT JOIN user u ON a.user_id = u.id
                        LEFT JOIN article_classify ac ON a.classify_id = ac.id
                        WHERE a.user_id = ?
                        LIMIT ?, ?
                        `;
      const result = await connection.execute(statement, [userId, offset, limit]);
      return result[0];
    } catch (error) {
      console.log(error);
    }
  }

  // 获取文章分类
  async classify(userId) {
    try {
      const statement = `SELECT 
                          ac.id id,
                          ac.classify classify,
                          count(a.classify_id) count
                        FROM article_classify ac
                        LEFT JOIN article a ON a.classify_id = ac.id
                        WHERE ac.user_id = ?
                        GROUP BY ac.id`;
      const result = await connection.execute(statement, [userId]);
      return result[0]
    } catch (error) {
      console.log(error);
    }
  }

  // 创建一个分类
  async createClassify(userId, classify) {
    try {
      const statement = `INSERT INTO article_classify (classify, user_id) VALUES (? ,?)`;
      const result = await connection.execute(statement, [classify, userId]);
      return result[0];
    } catch (error) {
      console.log(error)
    }
  }

  // 删除一个分类
  async deleteClassify(classifyId, userId) {
    try {
      const statement = `DELETE FROM article_classify WHERE id = ? AND user_id = ?`;
      const result = await connection.execute(statement, [classifyId, userId]);
      return result[0];
    } catch (error) {
      console.log(error)
    }
  }

  // 修改文章分类内容
  async updateClassify(classify, classifyId, userId) {
    try {
      const statement = `UPDATE article_classify SET classify = ? WHERE id = ? AND user_id = ?`;
      const result = await connection.execute(statement, [classify, classifyId, userId]);
      return result[0];
    } catch (error) {
      console.log(error)
    }
  }

  // 按照id获取分类文章
  async articles(userId, classifyId) {
    try {
      const statement = `SELECT 
                          id,
                          user_id userId,
                          title,
                          classify_id classifyId,
                          count,
                          view_count viewCount,
                          likes,
                          createAt createTime,
                          updateAt updateTime
                        FROM article a
                        WHERE a.user_id = ? AND a.classify_id = ?`;
      const result = await connection.execute(statement, [userId, classifyId]);
      return result[0];
    } catch (error) {
      console.log(error);
    }
  }

  // 分类是否存在
  async existClassify(classify, userId) {
    try {
      const statement = `SELECT id FROM article_classify WHERE classify = ? AND user_id = ?`;
      const result = await connection.execute(statement, [classify, userId]);
      return result[0];
    } catch (error) {
      console.log(error)
    }
  }

}

module.exports = new ArticleService();
